﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections;

namespace _Ajax上传文件解析
{
    public class ExcelUtil
    {
        /// <summary>
        /// 解析Excel
        /// </summary>
        /// <param name="filePath">Excel路径</param>
        /// <returns></returns>
        public static DataTable AnalysisExcel2007(string filePath, string sheetName = "Sheet1", bool isColumnNameWirtten = true)
        {
            XSSFWorkbook workbook;
            try
            {
                using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    workbook = new XSSFWorkbook(fs);
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

            ISheet sheetAt = workbook.GetSheet(sheetName);
            if (sheetAt == null)
            {
                sheetAt = workbook.GetSheetAt(0);
            }

            IEnumerator rowEnumerator = sheetAt.GetRowEnumerator();
            DataTable table = new DataTable();
            IRow row = sheetAt.GetRow(0);
            if (row != null)
            {
                for (int j = 0; j < row.LastCellNum; j++)
                {
                    ICell cell = row.GetCell(j);
                    if (cell == null)
                    {
                        table.Columns.Add("cell" + j.ToString());
                    }
                    else
                    {
                        table.Columns.Add(cell.ToString());
                    }
                }
            }
            int count = table.Columns.Count;
            for (int i = 0; rowEnumerator.MoveNext(); i++)
            {
                if (i > 0)
                {
                    IRow current = (XSSFRow)rowEnumerator.Current;
                    DataRow row3 = table.NewRow();
                    for (int k = 0; k < count; k++)
                    {
                        ICell cell2 = current.GetCell(k);
                        if (cell2 == null)
                        {
                            row3[k] = null;
                        }
                        else
                        {
                            row3[k] = cell2.ToString();
                        }
                    }
                    table.Rows.Add(row3);
                }
            }
            return table;


        }

        public static DataTable AnalysisExcel2003(string filePath, string sheetName = "Sheet1", bool isColumnNameWirtten = true)
        {
            HSSFWorkbook workbook;
            try
            {
                using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    workbook = new HSSFWorkbook(fs);
                }
            }
            catch (Exception ex)
            {
                return new DataTable();
            }

            ISheet sheetAt = workbook.GetSheet(sheetName);
            if (sheetAt == null)
            {
                sheetAt = workbook.GetSheetAt(0);
            }

            IEnumerator rowEnumerator = sheetAt.GetRowEnumerator();
            DataTable table = new DataTable();
            IRow row = sheetAt.GetRow(0);
            if (row != null)
            {
                for (int j = 0; j < row.LastCellNum; j++)
                {
                    ICell cell = row.GetCell(j);
                    if (cell == null)
                    {
                        table.Columns.Add("cell" + j.ToString());
                    }
                    else
                    {
                        table.Columns.Add(cell.ToString());
                    }
                }
            }
            int count = table.Columns.Count;
            for (int i = 0; rowEnumerator.MoveNext(); i++)
            {
                if (i > 0)
                {
                    IRow current = (HSSFRow)rowEnumerator.Current;
                    DataRow row3 = table.NewRow();
                    for (int k = 0; k < count; k++)
                    {
                        ICell cell2 = current.GetCell(k);
                        if (cell2 == null)
                        {
                            row3[k] = null;
                        }
                        else
                        {
                            row3[k] = cell2.ToString();
                        }
                    }
                    table.Rows.Add(row3);
                }
            }
            return table;

        }



        /// <summary>
        /// 导入数据到excel文件
        /// </summary>
        /// <param name="data">待导入的数据</param>
        /// <param name="fileName">文件路径</param>
        /// <param name="sheetName">excel表名</param>
        /// <param name="hiddenColmnIndexs">隐藏列索引</param>
        /// <param name="header">列头字典；原始列头=>重命名列名</param>
        /// <param name="redHeaderClomnIndexs">标记红色加粗的列索引</param>
        /// <param name="comments">列头批注字典；列名=>批注内容</param>
        /// <returns></returns>
        public static string DownLoadExcelByDataTable(DataTable data, string fileName, string sheetName, int[] hiddenColmnIndexs = null, Dictionary<string, string> header = null, int[] redHeaderClomnIndexs = null, Dictionary<string, string> comments = null)
        {
            int num4 = 0;
            int num = 0;
            int column = 0;
            int rownum = 0;
            ISheet sheet = null;
            IWorkbook workbook = new HSSFWorkbook();
            //todo:tans
            var stream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);

            try
            {
                sheet = workbook.CreateSheet(sheetName);


                IRow row = sheet.CreateRow(0);
                column = 0;
                int maxCount = Math.Max(header != null ? header.Count : 0, data.Columns.Count);

                IDrawing patr1 = sheet.CreateDrawingPatriarch();// as HSSFPatriarch;
                //创建列头
                while (column < data.Columns.Count)
                {
                    var col = data.Columns[column];
                    var colName = col.ColumnName;
                    ICell cell = row.GetCell(column);
                    if (header != null && header.ContainsKey(colName))
                    {
                        string temp = header[colName];//列重命名
                        header.Remove(colName);
                        cell = row.CreateCell(column);
                        cell.SetCellValue(temp);
                    }
                    //创建批注
                    colName = cell.StringCellValue;
                    if (comments != null && comments.ContainsKey(colName))
                    {
                        var commentStr = comments[colName];
                        IComment comment1 = patr1.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 3, 3, 7));// as HSSFComment;
                        comment1.String = new HSSFRichTextString(commentStr);
                        cell.CellComment = comment1;
                    }
                    column++;
                }
                //附加列头
                int colCount = data.Columns.Count;
                foreach (var colName in header.Keys)
                {
                    row.CreateCell(colCount).SetCellValue(header[colName]);
                    colCount++;
                }
                //设置列头样式
                redHeaderClomnIndexs = redHeaderClomnIndexs ?? new int[] { };
                for (int i = 0, l = 0, rl = redHeaderClomnIndexs.Length; i < row.Cells.Count; i++, l++)
                {
                    var cell = row.GetCell(i);
                    if (cell == null)
                    {
                        continue;
                    }
                    //设置字体
                    IFont font = workbook.CreateFont();
                    font.Boldweight = (short)FontBoldWeight.Bold;
                    if (l < rl)
                    {
                        int index = redHeaderClomnIndexs[l];
                        cell = row.GetCell(index);
                        font.Color = (short)FontColor.Red;
                    }
                    //设置样式
                    ICellStyle style = workbook.CreateCellStyle();
                    style.Alignment = HorizontalAlignment.Center;
                    style.SetFont(font);

                    cell.CellStyle = style;
                }
                //创建数据列，并填充数据
                rownum = 1;
                for (num = 0; num < data.Rows.Count; num++)
                {
                    IRow row2 = sheet.CreateRow(rownum);
                    for (column = 0; column < data.Columns.Count; column++)
                    {
                        row2.CreateCell(column).SetCellValue(data.Rows[num][column].ToString());
                    }
                    rownum++;
                }
                //设置隐藏列
                if (hiddenColmnIndexs != null && hiddenColmnIndexs.Length <= column)
                {
                    for (int i = 0, l = hiddenColmnIndexs.Length; i < l; i++)
                    {
                        sheet.SetColumnHidden(hiddenColmnIndexs[i], true);
                    }
                }
                //当创建完毕所有cell，设置居中才有效
                for (int i = 0; i < row.Cells.Count; i++)
                {
                    sheet.AutoSizeColumn(i);
                }
                workbook.Write(stream);
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                stream.Close();
            }
            return fileName;
        }



        /// <summary>
        /// 删除Excel
        /// </summary>
        /// <param name="filePath">Excel路径</param>
        public static void DeleteExcel(string filePath)
        {
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
        }

    }


}